// 导入数据库操作模块
const db = require('../db/index')



// 获取学生列表
exports.getStudentList = async (req,res) => {
    try {
        const query = `
            SELECT s.*, d.name AS dormitory_name, d.address AS dormitory_address, d.capacity AS dormitory_capacity
            FROM students s
            LEFT JOIN dormitories d ON s.dormitory_id = d.id
        `;
        
        const [rows] = await db.query(query); 
        console.log(rows);
        
        
        const students = rows.map(row => ({
            student_id: row.student_id,
            college_id: row.college_id,
            major: row.major,
            name: row.name,
            dormitory: row.dormitory_id ? {
                id: row.dormitory_id,
                name: row.dormitory_name,
                address: row.dormitory_address,
                capacity: row.dormitory_capacity
            } : null
        }));
        
        res.send({
            status: 0,
            message: '获取学生列表成功',
            data: students
        });
    } catch (error) {
        console.error(error);
        res.status(500).send({
            status: 1,
            message: '服务器错误'
        });
    }
}

// 添加学生
exports.addStudent = async (req, res) => {
    try {
        const studentInfo = req.body;
        console.log(studentInfo);
        
        if (!studentInfo.name || !studentInfo.student_id || !studentInfo.major) {
            return res.send({ 
                status: 1, 
                message: '姓名、学号、专业和学院编号不能为空' 
            });
        }

        const [existingStudents] = await db.query(
            'SELECT * FROM students WHERE student_id = ?',
            [studentInfo.student_id]
        );

        if (existingStudents.length > 0) {
            return res.send({ 
                status: 1, 
                message: '该学号已存在' 
            });
        }


        const insertData = {
            name: studentInfo.name,
            student_id: studentInfo.student_id,
            college_id: 1,
            major: studentInfo.major,
            dormitory_id: studentInfo.dormitory_id || null,
        };

        // 执行插入操作
        const [result] = await db.query(
            'INSERT INTO students SET ?',
            [insertData]
        );

        if (result.affectedRows !== 1) {
            return res.send({ 
                status: 1, 
                message: '添加学生失败' 
            });
        }

        // 返回成功响应
        res.send({
            status: 0,
            message: '添加学生成功',
            data: {
                id: result.insertId,
                ...insertData
            }
        });

    } catch (error) {
        console.error('添加学生错误:', error);
        res.status(500).send({
            status: 1,
            message: '服务器错误'
        });
    }
};

// 删除学生
exports.deleteStudent = async (req, res) => {
    try {
        // 获取要删除的学生ID
        const studentId = req.body.student_id;
        console.log(studentId);
        
        // 数据验证
        if (!studentId) {
            return res.send({ 
                status: 1, 
                message: '学生ID不能为空' 
            });
        }

        // 执行删除操作
        const [result] = await db.query(
            'DELETE FROM students WHERE student_id = ?',
            [studentId]
        );

        if (result.affectedRows !== 1) {
            return res.send({ 
                status: 1, 
                message: '删除学生失败' 
            });
        }

        // 返回成功响应
        res.send({
            status: 0,
            message: '删除学生成功'
        });

    } catch (error) {
        console.error('删除学生错误:', error);
        res.status(500).send({
            status: 1,
            message: '服务器错误'
        });
    }
}

// 根据名字模糊查询学生
exports.getStudentByName = async (req, res) => {
    try {
        // 获取查询参数
        const name = req.query.name;
        console.log(name);
        
        // 数据验证
        if (!name) {
            return res.send({ 
                status: 1, 
                message: '姓名不能为空' 
            });
        }

        // 使用JOIN查询关联信息
        const query = `
            SELECT s.*, d.name AS dormitory_name, d.address AS dormitory_address, d.capacity AS dormitory_capacity
            FROM students s
            LEFT JOIN dormitories d ON s.dormitory_id = d.id
            WHERE s.name LIKE ?
        `;
        
        // 执行查询操作
        const [rows] = await db.query(query, [`%${name}%`]);
        console.log(rows);
        
        // 格式化返回数据
        const students = rows.map(row => ({
            student_id: row.student_id,
            college_id: row.college_id,
            major: row.major,
            name: row.name,
            dormitory:{
                id: row.dormitory_id,
                name: row.dormitory_name,
                address: row.dormitory_address,
                capacity: row.dormitory_capacity
            }
        }));

        // 返回查询结果
        res.send({
            status: 0,
            message: '查询成功',
            data: students
        });

    } catch (error) {
        console.error('查询学生错误:', error);
        res.status(500).send({
            status: 1,
            message: '服务器错误'
        });
    }
}